package com.hnust.dao;

import com.hnust.domain.Paper;
import com.hnust.domain.accept.PaperData;
import com.hnust.domain.record.PaperDataInfo;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

import com.hnust.domain.record.PageData;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 创建人：gh
 * 创建时间：2020/10/29
 * 描述：
 */
@Mapper
@Repository
public interface PaperDao {

    //test
    @Select("select name from paper where pap_id='1011574362'")
    String test();

    /**
     * 新-获取所有试卷
     */
    @SelectProvider(type = getAllExamProvider.class, method = "getAllExamSql")
    List<PaperDataInfo> getAllExam();
    class getAllExamProvider{
        public String getAllExamSql(){
            String sql = "select paper.pap_id as papId, paper.name as name,  course.name as courseName,  course_group.open_time as openTime,\n" +
                    "paper.time as createTime,teacher.name as author,college.name as collegeName,\n" +
                    "paper.is_sample as isSample, paper.is_a_b as isAB,\n" +
                    "test.beginTime as beginTime,\n" +
                    "test.beginTime as endTime\n" +
                    "from paper,teacher,course,course_group,college,test\n" +
                    "where teacher.tea_id = paper.pro_teacher\n" +
                    "and course.cou_number = paper.cou_number\n" +
                    "and course.cou_id = course_group.cou_id\n" +
                    "and course.col_id = college.col_id \n" +
                    "and paper.totestid = test.topaperid";

            return sql;
        }
    }

    @SelectProvider(type = getExamListProvider.class, method = "getExamListSql")
    List<PaperDataInfo> getExamList(String subject, String pap_name);
    class getExamListProvider{
        public String getExamListSql(String subject, String pap_name){
            //根据课程名的查询
            String sql1 = "select paper.pap_id as papId, paper.name as name,  course.name as courseName,  course_group.open_time as openTime, " +
                    "paper.time as createTime,teacher.name as author,college.name as collegeName, " +
                    "paper.is_sample as isSample, paper.is_a_b as isAB, " +
                    "test.beginTime as beginTime, " +
                    "test.beginTime as endTime " +
                    "from paper,teacher,course,course_group,college,test " +
                    "where teacher.tea_id = paper.pro_teacher " +
                    "and course.cou_number = paper.cou_number " +
                    "and course.cou_id = course_group.cou_id " +
                    "and course.col_id = college.col_id " +
                    "and paper.totestid = test.topaperid "+
                    "and course.name = #{subject}";
            //根据试卷名的查询
            String sql2 = "select paper.pap_id as papId, paper.name as name,  course.name as courseName,  course_group.open_time as openTime,\n" +
                    "paper.time as createTime,teacher.name as author,college.name as collegeName,\n" +
                    "paper.is_sample as isSample, paper.is_a_b as isAB,\n" +
                    "test.beginTime as beginTime,\n" +
                    "test.beginTime as endTime\n" +
                    "from paper,teacher,course,course_group,college,test\n" +
                    "where teacher.tea_id = paper.pro_teacher\n" +
                    "and course.cou_number = paper.cou_number\n" +
                    "and course.cou_id = course_group.cou_id\n" +
                    "and course.col_id = college.col_id \n" +
                    "and paper.totestid = test.topaperid \n"+
                    "and test.test_name = #{pap_name}";

            //两者同时满足的查询
            String sql3="select paper.pap_id as papId, paper.name as name,  course.name as courseName,  course_group.open_time as openTime,\n" +
                    "paper.time as createTime,teacher.name as author,college.name as collegeName,\n" +
                    "paper.is_sample as isSample, paper.is_a_b as isAB,\n" +
                    "test.beginTime as beginTime,\n" +
                    "test.beginTime as endTime\n" +
                    "from paper,teacher,course,course_group,college,test\n" +
                    "where teacher.tea_id = paper.pro_teacher\n" +
                    "and course.cou_number = paper.cou_number\n" +
                    "and course.cou_id = course_group.cou_id\n" +
                    "and course.col_id = college.col_id \n" +
                    "and paper.totestid = test.topaperid \n"+
                    "and course.name = #{subject} \n"+
                    "and test.test_name = #{pap_name}";

            String sql="";
            if(!subject.isEmpty()&& pap_name.isEmpty()){
                //只有课程名，根据课程名来搜
                sql=sql1;
            }
            else if (subject.isEmpty() && !pap_name.isEmpty()){
                //只有考试名称，根据考试名称来搜
                sql=sql2;
            }
            else if (!subject.isEmpty() && !pap_name.isEmpty()){
                //两者都有，则需要同时满足
                sql=sql3;
            }
            return sql;
        }
    }
    /**
     * //试卷列表---删除试卷
     * @param paper_id 试卷id
     * @return // 返回影响的行数，如果删除成功则应该是影响1行，如果是删除失败则应该是0行
     */
    @Delete("delete from paper where pap_id = #{paper_id}")
    int delTestPaper(String paper_id);

    /**
     *  添加试卷信息
     * @param paper 试卷数据
     * @return 返回插入的个数
     */
    @Insert("insert into paper (pap_id,pass_rate,name,pro_teacher,time,exam_time,cou_number,grade,is_sample,is_a_b,college_id) " +
            "values (#{papId},#{passRate},#{name},#{proTeacher},#{time},#{examTime},#{couNumber},#{grade},#{isSample},#{isAb},#{collegeId})")
    Integer addPaper(Paper paper);
    
    /** 
     * 试卷列表 --- 获取试卷列表，要判断course_id是不是空的
     * 采用了分页，默认是一页8条数据
     * @param now_set 从now_set开始获取8条数据
     * @param course_id 课程id
     * @param id 老师id
     * @return PageData对象
     */
    @SelectProvider(type = getPaperListProvider.class, method = "getPaperListSql")
    @ResultType(PageData.class)
    List<PageData> getPaperList(int now_set, String course_id, String id);
    class getPaperListProvider{ // 这里要根据course_id是不是空的来判断是不是获取全部课程的试卷列表
        public String getPaperListSql(int now_set, String course_id, String id){
            String sql = "select pap_id, paper.name as name, teacher.name as tea_name, paper.time as create_time, is_sample, is_a_b, college.name as college_name, course.name as course_name "+
                "from paper, teacher, course, college "+
                "where teacher.tea_id = paper.pro_teacher and course.cou_number = paper.cou_number and paper.pro_teacher = #{id} and paper.college_id=college.col_id and course.cou_id=#{course_id} "+
                "limit #{now_set},8";
            if(course_id == null){ // course_id是空的，所以是查找所有课程的
                sql = "select pap_id, paper.name as name, teacher.name as tea_name, paper.time as create_time, is_sample, is_a_b, college.name as college_name, course.name as course_name "+
                        "from paper, teacher, course, college "+
                        "where teacher.tea_id = paper.pro_teacher and course.cou_number = paper.cou_number and paper.pro_teacher = #{id} and paper.college_id=college.col_id "+
                        "limit #{now_set},8";
            }
            return sql;
        }
    }

    /**
     * 试卷列表 --- 获取试卷的总的量，注意course_id可能为null
     * @param course_id 课程id
     * @param id 老师id
     * @return 返回总数
     */
    @SelectProvider(type = getPaperListCountProvider.class, method = "getPaperListCountSql")
    int getPaperListCount(String course_id, String id);
    class getPaperListCountProvider{
        public String getPaperListCountSql(String course_id, String id){
            String sql = "select count(*) "+
                    "from paper, teacher, course, college "+
                    "where teacher.tea_id = paper.pro_teacher and course.cou_number = paper.cou_number and paper.pro_teacher = #{id} and paper.college_id=college.col_id and course.cou_id=#{course_id}";
            if(course_id == null){ // course_id是空的，所以是查找所有课程的
                sql = "select count(*) "+
                        "from paper, teacher, course, college "+
                        "where teacher.tea_id = paper.pro_teacher and course.cou_number = paper.cou_number and paper.pro_teacher = #{id} and paper.college_id=college.col_id";
            }
            return sql;
        }
    }

    /**
     * 试卷列表 --- 设置试卷为样卷
     * @param pap_id 试卷id
     * @return 返回影响行数
     */
    @Update("update paper set is_sample = 1 where pap_id = #{pap_id}")
    int setPaperToSample(String pap_id);

    /**
     * 试卷列表 --- 设置试卷为样卷
     * @param pap_id 试卷id
     * @return 返回影响行数
     */
    @Update("update paper set is_sample = 0 where pap_id = #{pap_id}")
    int removePaperToSample(String pap_id);

}
